package com.gmrz.uaf.db.dao;

import com.gmrz.uaf.common.AAIDJson;
import com.gmrz.uaf.common.PoliceJson;
import com.gmrz.uaf.db.DAOException;
import com.gmrz.uaf.protocol.v1.processor.exception.UAFErrorCode;
import com.gmrz.util.CryUtil;
import com.gmrz.util.Strings;
import com.gmrz.util.db.DBUtil;
import com.google.inject.assistedinject.Assisted;
import com.google.inject.assistedinject.AssistedInject;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import static com.gmrz.uaf.common.Constants.POLICY_NAME_PREFIX;

/**
 * Created by yangairong on 16/8/29.
 */
public class BaseUasConfigDAO implements UasConfigDAO {

    private static final Logger LOG = LogManager.getLogger(BaseDevicesDAO.class);
    private Connection conn_;
    private static final String _SQL_SELECT_ALL ="select config_ename,config_value,config_default_value from t_uas_config";
    private static final String _SQL_SELECTFacts_ALL="select facet_name,auth_code from t_facets where tenant_id=?";
    private static final String _SQL_SELECTPolice_ALL="select * from t_policy where auth_type<30";//30以上为fido2
    private static final String _SQL_SELECTAAID_ALL="select b.aaid from t_policy_auth a,t_auth_metadata b where a.metadata_id=b.metadata_id and b.protocol='uaf' and a.pl_id=?";
    private static final String _SQL_SELECTTenantid_List="select tenant_id from t_tenant";
    private static final String _SQL_SELECT_CONFIG_BY_TENANT_ID="SELECT config_value from t_tenant_config WHERE config_ename = ? and tenant_id = ?";

    @AssistedInject
    public BaseUasConfigDAO(@Assisted Connection conn_) {
        this.conn_ = conn_;
    }

    //数据库中加载uas所有的属性信息（配置信息，例如redis的配置信息）
    public Properties loadUasConfig() throws DAOException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Properties uafProperties = new Properties();
        try {
            LOG.debug("get config from db start");
            ps = this.conn_.prepareStatement(_SQL_SELECT_ALL);
            rs = ps.executeQuery();
            while (rs.next()){
                String v = rs.getString("config_value");
                LOG.debug("db config v:"+v);
                if(Strings.isNullOrEmpty(v)) {
                    if(Strings.isNullOrEmpty(rs.getString("config_default_value"))){
                        uafProperties.put(rs.getString("config_ename"), "");
                    }else{
                        uafProperties.put(rs.getString("config_ename"), rs.getString("config_default_value"));
                    }
                }else {
                    uafProperties.put(rs.getString("config_ename"), rs.getString("config_value"));
                }
            }
            return uafProperties;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}", _SQL_SELECT_ALL);
            throw new DAOException(UAFErrorCode.DB_SYSTEM_CONFIG_LOAD_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    public HashMap<String,String> loadFacetsTenantID() throws DAOException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        String tenantid="";
        String factesId="";
        HashMap<String,String> facetsLst=new HashMap<String, String>();
        try {
            ps = this.conn_.prepareStatement(_SQL_SELECTTenantid_List);
            rs = ps.executeQuery();
            while (rs.next()){
                tenantid=rs.getString("tenant_id").trim();
                factesId= loadFacets(tenantid);
                if(factesId.length()>0){
                    facetsLst.put(tenantid,factesId);
                }
            }
            return facetsLst;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}", _SQL_SELECTTenantid_List,exp);
            throw new DAOException(UAFErrorCode.DB_FACET_LOAD_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    //从数据库中加载tenantid中的facets的信息到列表中
    public String loadFacets(String tenantid) throws DAOException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        String factesid="";
        try {
            ps = this.conn_.prepareStatement(_SQL_SELECTFacts_ALL);
            ps.setString(1,tenantid);
            rs = ps.executeQuery();
            while (rs.next()){
                String facetName = rs.getString("facet_name");
                String facetNameCryptograph = rs.getString("auth_code");
                if(checkFacteId(facetName,facetNameCryptograph)) {
                    factesid = factesid + rs.getString("facet_name") + ";";
                }
            }
            if(factesid.length()>0)
            factesid = factesid.substring(0, factesid.length() - 1);
            return factesid;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}", _SQL_SELECTFacts_ALL);
            throw new DAOException(UAFErrorCode.DB_FACET_LOAD_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    private boolean checkFacteId(String facetName, String facetNameCryptograph) {
        if(Strings.isNotBlank(facetNameCryptograph)) {
            String[] ftArrays = facetNameCryptograph.split("\\*");
            if(ftArrays.length == 2) {
                String facetNameDecryt = CryUtil.decryptMetaData(facetNameCryptograph);
                if (Strings.isNotBlank(facetNameDecryt) && Strings.isNotBlank(facetName) && facetName.equals(facetNameDecryt)) {
                    return true;
                }
            }else if(ftArrays.length == 4){
                String facetNameDecryt = CryUtil.decryptMetaData(ftArrays[0]+"*"+ftArrays[1]);
                if(Strings.isNotBlank(facetNameDecryt) && Strings.isNotBlank(facetName)){
                    if(facetNameDecryt.startsWith("needtime")){
                        String[] sArrays = facetNameDecryt.split("\\*");
                        Date date = Strings.strToDate(sArrays[1],"yyyy-MM-dd");
                        if(sArrays[2].equals(facetName) && date.getTime() > System.currentTimeMillis()){
                            return true;
                        }
                    }
                }
            }
        }
        return false;
    }

    //police认证器类型选择
    //通过数据库获取数据。拼成相应格式的字符串存放在列表中
    public HashMap<String,PoliceJson> LoadPolicyJson() throws DAOException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        String policyname="";
        String plid="";
        HashMap<String,PoliceJson> jsonStr=new HashMap<String, PoliceJson>();
        try {
            ps = this.conn_.prepareStatement(_SQL_SELECTPolice_ALL);
            rs = ps.executeQuery();
            while (rs.next()){
                PoliceJson policeJsonVal=new PoliceJson();
                policyname=POLICY_NAME_PREFIX+rs.getString("tenant_id")+"_"+rs.getString("auth_type")+"_"+rs.getString("trans_type");
                plid= rs.getString("pl_id");
                policeJsonVal.setAccepted(LoadAAIDJson(plid));
                if(!policeJsonVal.getAccepted().isEmpty())
                  jsonStr.put(policyname,policeJsonVal);
            }
            return jsonStr;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}", _SQL_SELECTPolice_ALL);
            throw new DAOException(UAFErrorCode.DB_POLICY_LOAD_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    public List<List<AAIDJson>> LoadAAIDJson(String id) throws DAOException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        String aaidvalue="";
        List<List<AAIDJson>> ResultJson=new ArrayList<List<AAIDJson>>();
        try {
            ps = this.conn_.prepareStatement(_SQL_SELECTAAID_ALL);
            ps.setString(1,id);
            rs = ps.executeQuery();
            while (rs.next()){
                List<AAIDJson> AAIDLst=new ArrayList<AAIDJson>();
                aaidvalue=rs.getString("aaid");
                AAIDJson aaid=new AAIDJson();
                aaid.setAaid(Arrays.asList(aaidvalue));
                AAIDLst.add(aaid);
                ResultJson.add(AAIDLst);
            }
            return ResultJson;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}", _SQL_SELECTAAID_ALL);
            throw new DAOException(UAFErrorCode.DB_POLICY_LOAD_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    public List loadfacetsServlet(String tenantid) throws DAOException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        List list=new ArrayList();
        try {
            ps = this.conn_.prepareStatement(_SQL_SELECTFacts_ALL);
            ps.setString(1,tenantid);
            rs = ps.executeQuery();
            while (rs.next()){
                list.add(Strings.escapeHtmlAndScript(rs.getString("facet_name")));
            }
            return list;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}", _SQL_SELECTFacts_ALL);
            throw new DAOException(UAFErrorCode.ERROR_DEVICE_NOT_FOUND,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    @Override
    public String getConfigByTenantId(String key, String tenantId) throws DAOException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        String config = null;
        try {
            ps = this.conn_.prepareStatement(_SQL_SELECT_CONFIG_BY_TENANT_ID);
            ps.setString(1,key);
            ps.setString(2,tenantId);
            rs = ps.executeQuery();
            while (rs.next()){
                config = rs.getString("config_value");
                LOG.info("getConfigByTenantId:"+key+","+tenantId+","+config);
            }
            return config;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}", _SQL_SELECT_CONFIG_BY_TENANT_ID);
            throw new DAOException(UAFErrorCode.ERROR_DEVICE_NOT_FOUND,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    @Override
    public String getConfigByTenantIdAndTransType(String key, String tenantId,String transType) throws DAOException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        String config = null;
        try {
            ps = this.conn_.prepareStatement(_SQL_SELECT_CONFIG_BY_TENANT_ID+" and trans_type = ?");
            ps.setString(1,key);
            ps.setString(2,tenantId);
            ps.setString(3,transType);
            rs = ps.executeQuery();
            while (rs.next()){
                config = rs.getString("config_value");
                LOG.info("getConfigByTenantId:"+key+","+tenantId+","+config);
            }
            return config;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}", _SQL_SELECT_CONFIG_BY_TENANT_ID);
            throw new DAOException(UAFErrorCode.ERROR_DEVICE_NOT_FOUND,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    @Override
    public String getdictTypeByDictName(String dictName) {
        String sql = "select dict_text from t_dict where dt_id = '00dcb889f5364e5f850b79617cae168d' and dict_name = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        String dictText = null;
        try {
            ps = this.conn_.prepareStatement(sql);
            ps.setString(1, dictName);
            rs = ps.executeQuery();
            while (rs.next()){
                dictText = rs.getString("dict_text");
            }
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}",sql);
            //throw new DAOException(UAFErrorCode.DB_QUERY_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
        return dictText;
    }

    @Override
    public List<String> getUserConfig(String dtId) throws DAOException {
        String sql = "SELECT dict_text from t_dict WHERE dt_id = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<String> resultList = new ArrayList<String>();
        try {
            ps = this.conn_.prepareStatement(sql);
            ps.setString(1,dtId);
            rs = ps.executeQuery();
            while (rs.next()){
                resultList.add(rs.getString("dict_text"));
            }
            return resultList;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}",sql);
            throw new DAOException(UAFErrorCode.DB_QUERY_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    @Override
    public List<String> getShareAppID(String applicationID,String transType) throws DAOException {
        String sql = "SELECT  tenant_id  from t_tenant_config WHERE config_ename = 'uaf.application.id' and config_value = ? and trans_type = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<String> resultList = new ArrayList<String>();
        try {
            ps = this.conn_.prepareStatement(sql);
            ps.setString(1,applicationID);
            ps.setString(2,transType);
            rs = ps.executeQuery();
            while (rs.next()){
                resultList.add(rs.getString("tenant_id"));
            }
            return resultList;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}",sql);
            throw new DAOException(UAFErrorCode.DB_QUERY_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    @Override
    public String getConfigByKey(String key) throws DAOException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        String config = null;
        String sql = "select config_ename,config_value,config_default_value from t_uas_config WHERE config_ename = ?";
        try {
            ps = this.conn_.prepareStatement(sql);
            ps.setString(1,key);
            rs = ps.executeQuery();
            while (rs.next()){
                config = rs.getString("config_value");
                LOG.info("getConfigByKey:"+key+","+config);
            }
            return config;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}", sql);
            throw new DAOException(UAFErrorCode.DB_SYSTEM_CONFIG_LOAD_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }

    @Override
    public List<String> getTenantids() throws DAOException{
        PreparedStatement ps = null;
        ResultSet rs = null;
        String tenantid="";
        List<String> tenantids = new ArrayList<String>();
        try {
            ps = this.conn_.prepareStatement(_SQL_SELECTTenantid_List);
            rs = ps.executeQuery();
            while (rs.next()){
                tenantid=rs.getString("tenant_id").trim();
                if(Strings.isNotBlank(tenantid)){
                    tenantids.add(tenantid);
                }
            }
            return tenantids;
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}", _SQL_SELECTTenantid_List);
            throw new DAOException(UAFErrorCode.DB_FACET_LOAD_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
    }


    @Override
    public String getDictConfig(String key) throws DAOException {
        String sql = "SELECT dict_text from t_dict WHERE dict_name = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = this.conn_.prepareStatement(sql);
            ps.setString(1,key);
            rs = ps.executeQuery();
            while (rs.next()){
                return rs.getString("dict_text");
            }
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}",sql);
            throw new DAOException(UAFErrorCode.DB_QUERY_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
        return null;
    }

    @Override
    public String getPolicyConfig(String appID, String authType, String transType, String key) throws DAOException {
        String sql = "SELECT pc.config_value from t_policy p,t_policy_config pc where p.pl_id = pc.policy_id and p.tenant_id = ? and p.auth_type = ? and p.trans_type = ? and pc.config_name = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = this.conn_.prepareStatement(sql);
            ps.setString(1,appID);
            ps.setString(2,authType);
            ps.setString(3,transType);
            ps.setString(4,key);
            rs = ps.executeQuery();
            while (rs.next()){
                return rs.getString("config_value");
            }
        } catch (SQLException exp) {
            LOG.error("The following SQL statement failed to execute :\n {}",sql);
            throw new DAOException(UAFErrorCode.DB_QUERY_FAILED,exp);
        } finally {
            DBUtil.close(rs);
            DBUtil.close(ps);
        }
        return null;
    }
}
